package com.ruoyi.aitcommon.utils;

import java.sql.*;
import java.util.List;
import java.util.Map;

import com.ruoyi.common.utils.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;

/**
 * SQLUtils父类
 * 封装SQL CRUD基本操作
 * 
 * @author Administrator
 *
 */
@Component
public class SQLBase {
//	静态方法里边如何使用spring的注入对象
//	参考文档 : https://blog.csdn.net/Jarbein/article/details/117857580
	private static SQLBase sqlBase;
	@Autowired
	private RuntimeManager runtimeManager;

	@PostConstruct
	public void init(){
		sqlBase = this;
		sqlBase.runtimeManager = this.runtimeManager;
	}

	/**
	 * SQL基础更新方法
	 * @param sql
	 * @param db
	 * @return
	 */
	public static int execUpdate(String sql, DBUtils db) throws Exception {
		int ret = -1;
		long startTime = System.currentTimeMillis(); // 记录开始时间
		long endTime;
		long duration=0;
		Connection conn=null;
		PreparedStatement preState=null;

		try {
			if (StringUtils.isBlank(sql))
				throw new Exception("sql为空,请检查配置!");
			if(db==null)
				throw new Exception("数据库配置为空,请检查配置!");

//			日志输出
			LogUtils.sqlDBDebug(sql, db);
//			打开DB
			conn = db.open();
			preState = conn.prepareStatement(sql);
			ret=preState.executeUpdate();
		} catch (Exception ex) {
			LogUtils.sqlDebug("SQL错误,具体见ait-common");
			String msg = "SQL错误:%s\r\nSQL: %s\r\n DB: %s\r\n";
			msg = String.format(msg, ex.getMessage(), sql, db.toString());
			throw new Exception(msg,ex);
		}finally {
//			关闭DB
			if(preState!=null) preState.close();
			if(conn!=null) conn.close();

			endTime = System.currentTimeMillis(); // 记录执行结束时间
			duration = endTime - startTime;
			if(duration>AITConst.WARN_RUNTIME){
				LogUtils.perfWarn("[execUpdate]执行时间过长[{}]秒,db:{},sql:\r\n{}",duration/1000,db.toString(),sql);
			}
		}
		if(ret!=1){
			LogUtils.warn("更新记录数为[{}],请检查SQL",ret);
		}
		if(ret>AITConst.WARN_UPDATE_NUM){
			LogUtils.perfWarn("[execUpdate]数据过大[{}]条,db:{},sql:\r\n{}",ret,db.toString(),sql);
		}
		return ret;
	}
	
	/**
	 * SQL基础查询方法
	 * @param sql
	 * @param db
	 * @return
	 */
	public static List<Map<String, Object>> execQuery(String sql, DBUtils db) throws Exception {
		List<Map<String, Object>> retlist = null;
		long startTime = System.currentTimeMillis(); // 记录开始时间
		long endTime;
		long duration=0;
		Connection conn=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet = null;

		try {
			if (StringUtils.isBlank(sql))
				throw new Exception("sql为空,请检查配置!");
			if(db==null)
				throw new Exception("数据库配置为空,请检查配置!");
//			日志输出
			LogUtils.sqlDBDebug(sql, db);
			if(sqlBase!=null&&sqlBase.runtimeManager!=null){
				sqlBase.runtimeManager.addSQLInfo(sql,db);
			}
//			打开DB
			conn = db.open();
			preparedStatement = conn.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			retlist = ListMapUtils.fromResultSet(resultSet);

		} catch (Exception ex) {
			LogUtils.sqlDebug("SQL错误,具体见ait-common");
//			String msg = "SQL错误:%s\r\nSQL语句:[ %s ]";
			String msg = "SQL错误:%s";
			msg = String.format(msg, ex.getMessage(), sql);
			throw new Exception(msg,ex);
		}finally {
//			关闭DB
			if(resultSet!=null) resultSet.close();
			if(preparedStatement!=null) preparedStatement.close();
			if(conn!=null) conn.close();

			endTime = System.currentTimeMillis(); // 记录执行结束时间
			duration = endTime - startTime;
			if(duration>AITConst.WARN_RUNTIME){
				LogUtils.perfWarn("[execQuery]执行时间过长[{}]秒,db:{},sql:\r\n{}",duration/1000,db.toString(),sql);
			}
		}

		if(retlist.size()>AITConst.WARN_QUERY_NUM){
			LogUtils.perfWarn("[execQuery]查询数据过大[{}]条,db:{},sql:\r\n{}",retlist.size(),db.toString(),sql);
		}
		return retlist;
	}
	
	/**
	 * SQL批量更新方法,支持分批更新
	 * @param sqls 更新sql
	 * @param db db链接
	 * @return 更新条数
	 */
	public static int execUpdate(List<String> sqls, DBUtils db) throws Exception {
		final int BatchCnt=1000;
		int num = 0;
		int[] rets=null;
		int i=0;
		long startTime = System.currentTimeMillis(); // 记录开始时间
		long endTime;
		long duration=0;
		Connection conn=null;
		Statement state = null;

		try {
			if (sqls == null || sqls.size() == 0)
				return 0;

//			日志输出
			LogUtils.sqlDBDebug(sqls.toString(), db);
//			打开DB
			conn = db.open();
			conn.setAutoCommit(false);//关闭自动提交
			state = conn.createStatement();
			for(i=0;i<sqls.size();i++) {
				if(i%BatchCnt==0&&i!=0) {
					//每1000条,批次提交DB数据
					rets=state.executeBatch();
					for (int ret : rets) {
						num += ret;
					}
					conn.commit();//提交
					state.clearBatch();//清空批次
				}
//					添加SQL
				state.addBatch(sqls.get(i));
			}
			//批次提交剩下的数据
			rets=state.executeBatch();
			for (int ret : rets) {
				num += ret;
			}
			conn.commit();
			//打开自动提交
			conn.setAutoCommit(true);

		} catch (Exception ex) {
//			抛错后回滚数据
			if(conn!=null) conn.rollback();

			LogUtils.sqlDebug("SQL错误,具体见ait-common");
			String msg = "SQL错误:%s\r\nSQL: %s\r\n DB: %s\r\n";
			String sqlmsg=sqls.get(0)+"\r\n...total:"+sqls.size();
			msg = String.format(msg, ex.getMessage(), sqlmsg, db.toString());
			throw new Exception(msg,ex);
		} finally {
//			关闭DB
			if(state!=null) state.close();
			if(conn!=null) conn.close();

			endTime = System.currentTimeMillis(); // 记录执行结束时间
			duration = endTime - startTime;
			if(duration>AITConst.WARN_RUNTIME){
				LogUtils.perfWarn("批量[execUpdate]执行时间过长[{}]秒,db:{},sql条数:[{}]",duration/1000,db.toString(),sqls.size());
			}
		}

		if(num>AITConst.WARN_UPDATE_NUM){
			LogUtils.perfWarn("批量[execUpdate]更新数据过大[{}]条,db:{},sql条数:[{}]",num,db.toString(),sqls.size());
		}
		return num;
	}
	/**
	 * 调用存储过程查询
	 * 
	 * @param sql
	 * @param db
	 * @return
	 */
	public static List<Map<String, Object>> execProcQuery(String sql, DBUtils db) throws Exception {
		List<Map<String, Object>> retmap = null;
		long startTime = System.currentTimeMillis(); // 记录开始时间
		long endTime;
		long duration=0;
		Connection conn = null;
		CallableStatement callableStatement = null;
		ResultSet retset = null;

		try {
			if (StringUtils.isBlank(sql))
				throw new Exception("sql为空,请检查配置!");
			if (db == null)
				throw new Exception("数据库配置为空,请检查配置!");
//			日志输出
			LogUtils.sqlDBDebug(sql, db);
//			打开DB
			conn=db.open();
			callableStatement = conn.prepareCall(sql);

			if (db.getDBConVO().isORACLE()) {
//				Oracle数据库
//				第一个参数注册为游标返回
//				参考链接:https://www.cnblogs.com/icewee/articles/2019807.html
				callableStatement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
				callableStatement.execute();
				retset = (ResultSet) callableStatement.getObject(1);
			} else {
//				SQLServer,MySQL
				retset = callableStatement.executeQuery();
			}
			endTime = System.currentTimeMillis(); // 记录执行结束时间
			duration = endTime - startTime;
			if(duration>AITConst.WARN_RUNTIME){
				LogUtils.perfWarn("[execProcQuery]执行时间过长[{}]秒,sql:{}",duration/1000,sql);
			}

			retmap = ListMapUtils.fromResultSet(retset);

		} catch (Exception ex) {
			LogUtils.sqlDebug("SQL错误,具体见ait-common");
			String msg = "SQL错误:%s\r\nSQL语句: %s\r\nDB链接: %s\r\n";
			msg = String.format(msg, ex.getMessage(), sql, db.toString());
			throw new Exception(msg, ex);
		}finally {
//			关闭DB
			if(retset!=null) retset.close();
			if(callableStatement!=null) callableStatement.close();
			if(conn!=null) conn.close();
		}

		if(retmap.size()>AITConst.WARN_QUERY_NUM){
			LogUtils.perfWarn("[execProcQuery]查询数据过大[{}]条,db:{},sql:{}",retmap.size(),db.toString(),sql);
		}
		return retmap;
	}

	/**
	 * 调用存储过程更新
	 * 
	 * @param sql
	 * @param db
	 * @return
	 */
	public static int execProcUpdate(String sql, DBUtils db) throws Exception {
		int ret = 0;
		long startTime = System.currentTimeMillis(); // 记录开始时间
		long endTime;
		long duration=0;
		Connection conn = null;
		CallableStatement callableStatement = null;

		try {
			if (StringUtils.isBlank(sql))
				throw new Exception("sql为空,请检查配置!");
			if (db == null)
				throw new Exception("数据库配置为空,请检查配置!");

//			日志输出
			LogUtils.sqlDBDebug(sql, db);
//			打开DB
			conn=db.open();
			callableStatement = conn.prepareCall(sql);
			ret = callableStatement.executeUpdate();
		} catch (Exception ex) {
			LogUtils.sqlDebug("SQL错误,具体见ait-common");
			String msg = "SQL错误:%s\r\nSQL语句: %s\r\nDB链接: %s\r\n";
			msg = String.format(msg, ex.getMessage(), sql, db.toString());
			throw new Exception(msg, ex);
		} finally {
//			关闭DB
			if(callableStatement!=null) callableStatement.close();
			if(conn!=null) conn.close();

			endTime = System.currentTimeMillis(); // 记录执行结束时间
			duration = endTime - startTime;
			if(duration>AITConst.WARN_RUNTIME){
				LogUtils.perfWarn("[execProcUpdate]执行时间过长[{}]秒,db:{},sql:{}",duration/1000,db.toString(),sql);
			}
		}

		return ret;
	}
}
